﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PalcoCentral.Database
{
    //To be done by Diogo
    public class CourseDBAdapter
    {
        private static CourseDBAdapter instance = null;

        public static CourseDBAdapter getInstance()
        {
            if (instance == null)
            {
                instance = new CourseDBAdapter();
            }

            return instance;
        }

         private Database.PalcoCentraBDEntities entity;
         private CourseDBAdapter()
        {
            this.entity = ContextAdapterSingleton.getInstance().entity;
        }

        #region Course CRUD
        /// <summary>
        /// 
        /// </summary>
        /// <returns>sucess = true|false && res with course</returns>
        public Library.OperationStatus addCourse(course course)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                os.res = entity.course.Add(course);
                os.sucess = true;
                Library.Utils.saveState(entity);
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro ao adicionar disciplina à base de dados"; //TODO: Complete
            }
            return os;
        }

        /// <summary>
        /// Should have courseId 
        /// </summary>
        /// <param name="course"></param>
        /// <returns>sucess = true|false && res with updated course</returns>
        public Library.OperationStatus updateCourse(course course)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                Library.Utils.saveState(entity);
                os.sucess = true;
                //TODO
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a actualizar disciplina na base de dados"; //TODO: Complete
            }
            return os;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="courseId"></param>
        /// <returns>sucess = true|false</returns>
        public Library.OperationStatus deleteCourse(course c)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                //WORKING
                List<@class> x = entity.@class.Where(w => w.courseId == c.courseId).ToList(); ;
                foreach(var item in x){
                    entity.@class.Remove(item);
                }
                entity.course.Remove(c);
                Library.Utils.saveState(entity);
                os.sucess = true;
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a remover disciplina na base de dados. Verifique os campos."; //TODO: Complete
            }
            return os;
        }

        #endregion

        #region Class CRUD
        //Acredito que neste caso não vale a pena actualizar. Mais vale apagar e criar uma nova não?

        /// <summary>
        /// 
        /// </summary>
        /// <param name="courseId"></param>
        /// <returns>sucess = true|false && res with class </returns>
        public Library.OperationStatus addClassToCourse(course course,Database.@class myClass)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                //?
                //var SqlQueryRes = from c in entity.course
                //           where c.courseId == courseId
                //           select c;
                //course courseTemp= SqlQueryRes.SingleOrDefault();
                //os.res = courseTemp;
         //TODO: Completar
                myClass.courseId = course.courseId;
                myClass = entity.@class.Add(myClass);
                Library.Utils.saveState(entity);
                os.res = myClass;
                os.sucess = true;
           
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a inserir na base de dados."; //TODO: Complete
            }
            return os;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="courseId"></param>
        /// <param name="classId"></param>
        /// <returns>sucess = true|false</returns>
        public  Library.OperationStatus removeClassFromCourse(Database.@class myClass)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                //?
                //var SqlQueryRes = from c in entity.@class
                //                  where ((c.classId == classId) && (c.courseId == courseId))
                //                  select c;

                //entity.@class.Remove(SqlQueryRes.SingleOrDefault());
                entity.@class.Remove(myClass);
                Library.Utils.saveState(entity);
                os.sucess = true;
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a remover na base de dados."; //TODO: Complete
            }
            return os;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="courseId"></param>
        /// <returns>sucess = true|false && res with list of courses</returns>
        public  Library.OperationStatus showPersonCourses(Database.person person)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                List<course> listTemp = new List<course>();
                //?
               // List<course> listTemp = entity.course.Where(w => (w.person.Contains(person.personId) || w.student.Contains(person))).ToList();
                if (person.personRole.CompareTo("Teacher") == 0)
                {
                    listTemp = (from c in entity.course
                                   from p in c.person
                                   where p.personId == person.personId
                                   select c).ToList();
                }
                else
                {
                    listTemp = (from c in entity.course
                                   from p in c.student
                                   where p.personId == person.personId
                                   select c).ToList();
                }
                os.res = listTemp;
                os.sucess = true;
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a consultar a base de dados."; //TODO: Complete
            }
            return os;
        }
        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <param name="courseId"></param>
        /// <returns>sucess = true|false && res with list of Classes</returns>
        public Library.OperationStatus showClasses(int courseId)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                //?
                List<@class> listTemp = entity.@class.Where(w => w.courseId == courseId).ToList();
                os.res = listTemp;
                os.sucess = true;
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Erro a consultar a base de dados."; //TODO: Complete
            }
            return os;
        }


        #region Manage Persons In Course
        /// <summary>
        /// 
        /// </summary>
        /// <param name="courseId"></param>
        /// <returns>sucess = true|false</returns>
        public Library.OperationStatus addPersonToCourse(person person, int courseId, Library.PersonRole pr)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
               
                    if (pr.Equals(Library.PersonRole.Teacher))
                    {
                        course c = entity.course.Where(w => w.courseId == courseId).SingleOrDefault();
                        c.person.Add(person);
                        //Library.Utils.saveState(entity);
                        entity.SaveChanges();
                    }
                    else if (pr.Equals(Library.PersonRole.Student))
                    {
                        course c = entity.course.Where(w => w.courseId == courseId).SingleOrDefault();

                        c.student.Add(person.student);
                        entity.SaveChanges();
                        //c.student.Add((student)PersonDBAdapter.getInstance().personToStudent(person).res);
                        //Library.Utils.saveState(entity);
                    }
                    os.sucess = true;
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Impossivel adicionar associação à base de dados"; //TODO: Complete
            }
            return os;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="courseId"></param>
        /// <returns>sucess = true|false</returns>
        public  Library.OperationStatus removePersonFromCourse(person person, course c, Library.PersonRole pr)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                    //WORKING
                    if (person.personRole.CompareTo("Teacher")==0)
                    {
                        //course c = entity.course.Where(w => w.courseId == courseId).SingleOrDefault();
                        c.person.Remove(person);
                        //Library.Utils.saveState(entity);
                        entity.SaveChanges();
                    }
                    else if (person.personRole.CompareTo("Student")==0)
                    {
                        //course c = entity.course.Where(w => w.courseId == courseId).SingleOrDefault();

                        c.student.Remove(person.student);
                        //c.student.Remove((student)PersonDBAdapter.getInstance().personToStudent(person).res);
                        //Library.Utils.saveState(entity);
                        entity.SaveChanges();
                    }
                    os.sucess = true;
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Impossivel remover associação da base de dados."; //TODO: Complete
            }
            return os;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="courseId"></param>
        /// <returns>sucess = true|false && res with list of Persons </returns>
        public Library.OperationStatus showPersonsInCourse(course course)
        {
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {
                List<person> persons;
               
                    persons = (from c in entity.course
                                from p in c.person
                                where c.courseId == course.courseId
                                select p).ToList();

                    persons.AddRange((from c in entity.course
                                      from s in c.student
                                      where c.courseId == course.courseId
                                      select s.person).ToList());

                os.res = persons;
                os.sucess = true;
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = "Não existem pessoas nesta disciplina."; //TODO: Complete
            }
            return os;
        }


        #endregion


        /// <summary>
        /// 
        /// </summary>
        /// <param name="courseId"></param>
        /// <returns>sucess = true|false && res with list of course</returns>
        public  Library.OperationStatus searchCourse(string query)
        {
            if (!string.IsNullOrEmpty(query) && (query.ToLower().Contains(Library.CourseType.ClaConj.ToString().ToLower()) || Library.CourseType.ClaConj.ToString().ToLower().Contains(query.ToLower())))
            {
                query += Library.CourseType.ClaConj.toStringDB();
            }
            if (!string.IsNullOrEmpty(query) && (query.ToLower().Contains(Library.CourseType.Danca.ToString().ToLower()) || Library.CourseType.Danca.ToString().ToLower().Contains(query.ToLower())))
            {
                query += Library.CourseType.Danca.toStringDB();
            }
            if (!string.IsNullOrEmpty(query) && (query.ToLower().Contains(Library.CourseType.FormMusic.ToString().ToLower()) || Library.CourseType.FormMusic.ToString().ToLower().Contains(query.ToLower())))
            {
                query += Library.CourseType.FormMusic.toStringDB();
            }
            if (!string.IsNullOrEmpty(query) && (query.ToLower().Contains(Library.CourseType.Inst.ToString().ToLower()) || Library.CourseType.Inst.ToString().ToLower().Contains(query.ToLower())))
            {
                query += Library.CourseType.Inst.toStringDB();
            }
          
            Library.OperationStatus os = new Library.OperationStatus();
            try
            {

                // var SqlQueryRes = entity.person.Where(p => (p.personRole != "Admin" && RemoveDiacritics(p.personName).ToLower().Contains(query) && query.Contains(RemoveDiacritics(p.personName).ToLower()) && biQuery == p.bi));

                var SqlQueryRes = from p in entity.course
                                  where p.courseName.ToLower().Contains(query) || query.Contains(p.courseName.ToLower()) || p.courseType.ToLower().Contains(query) || query.Contains(p.courseType.ToLower())
                                  select p;
                List<course> courses = SqlQueryRes.ToList<course>();


                os.res = courses;
                os.sucess = true;
            }
            catch (Exception ex)
            {
                os.sucess = false;
                os.ex = ex;
                os.errorMessage = ""; //TODO: Complete
            }
            return os;
        }
    }
}
